﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PgBirdie.Core.Config;
using PgBirdie.Core.Extensions;
using PgBirdie.Core.Model.Database.Data;
using PgBirdie.Core.Model.Database.Structure;
using PgBirdie.Core.Service.DbObject;

namespace PgBirdie.Core.Service.Delta
{
    public class DeltaStaticDataDbObjectToSqlService : IDeltaStaticDataDbObjectToSqlService
    {
        private readonly IDbObjectToSqlService _dbObjectToSql;

        public DeltaStaticDataDbObjectToSqlService(IDbObjectToSqlService dbObjectToSql)
        {
            _dbObjectToSql = dbObjectToSql;
        }

        public string InsertDataRows(IDictionary<string, PgDataRow> dataRows)
        {
            if (dataRows.IsNullOrEmpty())
                return String.Empty;
            
            var sql = new StringBuilder(String.Format("-- Insert data rows.{0}", Environment.NewLine));

            foreach (var dataRow in dataRows.Values.OrderBy(x => x.Table.ForeignKeys.Count))
                sql.AppendFormat("INSERT INTO {0}\"{1}\" ({2}) VALUES ({3});{4}",
                                 _dbObjectToSql.GetSchemaName(dataRow.Table),
                                 dataRow.Table.Name,
                                 GetColumnNamesForInsert(dataRow),
                                 GetColumnValuesForInsert(dataRow),
                                 Environment.NewLine);

            return sql.ToString();
        }

        public string UpdateDataRows(IDictionary<string, PgDataRow> dataRows, PgDatabase target)
        {
            if (dataRows.IsNullOrEmpty())
                return String.Empty;

            var sql = new StringBuilder(String.Format("-- Update data rows.{0}", Environment.NewLine));

            foreach (var dataRow in dataRows.Values)
            {
                var changedDataColumns = new List<PgDataColumn>();

                foreach (var dataColumn in dataRow.DataColumns.Values)
                {
                    var targetDataColumn =
                        target.Schemas[dataRow.Table.Schema.Name].Tables[dataRow.Table.Name].DataRows[
                            dataRow.Uid].DataColumns[dataColumn.Name];

                    if (!dataColumn.Value.Equals(targetDataColumn.Value, StringComparison.Ordinal))
                        changedDataColumns.Add(dataColumn);
                }

                sql.AppendFormat("UPDATE {0}\"{1}\" SET {2} WHERE {3};{4}",
                                 _dbObjectToSql.GetSchemaName(dataRow.Table),
                                 dataRow.Table.Name,
                                 GetConditionForUpdate(changedDataColumns),
                                 GetWhereClause(dataRow),
                                 Environment.NewLine);
            }

            return sql.ToString();
        }

        public string DeleteDataRows(IDictionary<string, PgDataRow> dataRows)
        {
            if (dataRows.IsNullOrEmpty())
                return String.Empty;

            var sql = new StringBuilder(String.Format("-- Delete data rows.{0}", Environment.NewLine));

            foreach (var dataRow in dataRows.Values)
                sql.AppendFormat("DELETE FROM {0}\"{1}\" WHERE {2};{3}",
                                 _dbObjectToSql.GetSchemaName(dataRow.Table),
                                 dataRow.Table.Name,
                                 GetWhereClause(dataRow),
                                 Environment.NewLine);

            return sql.ToString();
        }

        private string GetColumnNamesForInsert(PgDataRow dataRow)
        {
            if (String.IsNullOrEmpty(dataRow.Uid) || dataRow.DataColumns.IsNullOrEmpty())
                throw new Exception("Primary key for data row cannot be null or empty.");

            var names = new List<string>();
            foreach (var dataColumn in dataRow.DataColumns.Values)
                if (!ExcludeColumn(dataColumn))
                    names.Add(String.Format("\"{0}\"", dataColumn.Name));

            return String.Join(", ", names.ToArray());
        }

        private string GetColumnValuesForInsert(PgDataRow dataRow)
        {
            if (String.IsNullOrEmpty(dataRow.Uid) || dataRow.DataColumns.IsNullOrEmpty())
                throw new Exception("Primary key for data row cannot be null or empty.");

            var values = new List<string>();
            foreach (var dataColumn in dataRow.DataColumns.Values)
                if (!ExcludeColumn(dataColumn))
                    values.Add(String.Format("{0}", dataColumn.Value));

            return String.Join(", ", values.ToArray());
        }

        private string GetConditionForUpdate(IEnumerable<PgDataColumn> dataColumns)
        {
            if (dataColumns.IsNullOrEmpty())
                throw new Exception("Data columns cannot be null or empty.");

            var items = new List<string>();
            foreach (var dataColumn in dataColumns)
                if (!ExcludeColumn(dataColumn))
                    items.Add(String.Format("\"{0}\" = {1}", dataColumn.Name, dataColumn.Value));

            return String.Join(", ", items.ToArray());
        }

        private string GetWhereClause(PgDataRow row)
        {
            var conditions = new List<string>();
            foreach (var columnName in row.Table.PrimaryKey.ColumnNames)
                conditions.Add(String.Format("\"{0}\" = {1}", columnName, row.DataColumns[columnName].Value));
            return String.Join(" AND ", conditions.ToArray());
        }

        private bool ExcludeColumn(PgDataColumn dataColumn)
        {
            var column = dataColumn;
            var names = Settings.Source.Schemas[column.Table.Schema.Name].Tables[column.Table.Name].ExcludeStaticDataColumnNames;
            if (names.IsNullOrEmpty())
                return false;

            if (names.Contains(column.Name))
                return true;

            return false;
        }
    }
}